﻿using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Model;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class JurisdictionDal
    {
        private readonly AppDbContext _dbContext;
        private string URL;
        public JurisdictionDal(AppDbContext dbContext)
        {
            dynamic jsonObject = JsonConvert.DeserializeObject(File.ReadAllText("appsettings.json"));
            URL = jsonObject.ConnectionStrings.SqlServer;
            _dbContext = dbContext;
        }
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="jurisdiction"></param>
        /// <returns></returns>
        public async Task<int> CreateAsync(Jurisdiction jurisdiction)
        {
            _dbContext.jurisdictions.Add(jurisdiction);
            return await _dbContext.SaveChangesAsync();
        }
        /// <summary>
        /// 根据id查询
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public async Task<Jurisdiction> GetByIdAsync(int juriId)
        {
            using (SqlConnection con = new SqlConnection(URL))
            {
                string sql = $"Select * FROM dbo.sys_jurisdiction where jur_id={juriId}";
                return (await con.QueryAsync<Jurisdiction>(sql)).FirstOrDefault();
            }
        }
        /// <summary>
        /// 查询全部
        /// </summary>
        /// <returns></returns>
        public async Task<List<Jurisdiction>> GetAllAsync()
        {
            using(SqlConnection con=new SqlConnection(URL))
            {
                string sql = "Select * FROM dbo.sys_jurisdiction";
                return (await con.QueryAsync<Jurisdiction>(sql)).ToList();
            }
        }
        /// <summary>
        /// 根据父模块ID过滤查询
        /// </summary>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public async Task<List<Jurisdiction>> GetByGroupIdAsync(int groupId)
        {
            return await _dbContext.jurisdictions
                .Where(j => j.GroupID == groupId)
                .ToListAsync();
        }
        /// <summary>
        /// 目前只支持修改地址，如果要修改其他地址请来找我
        /// </summary>
        /// <param name="jurisdiction"></param>
        /// <returns></returns>
        public async Task<int> UpdateAsync(Jurisdiction jurisdiction)
        {
            using (SqlConnection con = new SqlConnection(URL))
            {
                string sql = $"UPDATE dbo.sys_jurisdiction SET jur_address = '{jurisdiction.JurAddress}' WHERE jur_id = {jurisdiction.JurID};";
                return (await con.ExecuteAsync(sql));
            }
        }
        /// <summary>
        /// 根据id删除
        /// </summary>
        /// <param name="juriId"></param>
        /// <returns></returns>
        public async Task<int> DeleteAsync(int juriId)
        {
            var jurisdiction = await GetByIdAsync(juriId);
            _dbContext.jurisdictions.Remove(jurisdiction);
            return await _dbContext.SaveChangesAsync();
        }
    }
}
